package com.presalept.manipulatedata;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.presalept.polodo.UserDO;

import net.sf.json.*;

public class RetrieveUserData {

	private String dbOneUserJson;
	private String dbAllUserJson;

	private List<UserDO> UserList;
	
	//get all users;
	
	public String GetUserlist()throws SQLException{
		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select * from userinfo";
		
		try{
		
			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbRs = dbPreStmt.executeQuery();	
			
			UserList = CreateUserlist(dbRs);
			
			dbAllUserJson = CreateUserlistJson(UserList);			
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}
		return dbAllUserJson;
		
	}
	
	//get one user of inputting userid
	
	public String GetOneUser(int UserId) throws SQLException{
		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select * from event where userid = ?";
		
		try{

			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbPreStmt.setInt(1, UserId);
			
			dbRs = dbPreStmt.executeQuery();	
			
			dbOneUserJson = CreateOneUserJson(CreateOneUser(dbRs));			
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}
		return dbOneUserJson;	
		
	}
	
	
	//get all users
	private List<UserDO> CreateUserlist(ResultSet dbRs) throws SQLException{
		
		List<UserDO> userlist = new ArrayList<UserDO>();
		
		while(dbRs.next()){
			UserDO userdo = new UserDO();
			userdo.setUserId(dbRs.getInt("userid"));
			userdo.setUserName(dbRs.getString("username"));
			userdo.setPassword(dbRs.getString("password"));
			userdo.setEmail(dbRs.getString("email"));
			userdo.setJoinDate(dbRs.getString("joindate"));
			userlist.add(userdo);	
		}		
		return userlist;
	}
	
	//get all users json
	
	private String CreateUserlistJson(List<UserDO> userlist){
		
		/*dbJasonAll = listPmpdAll.get(0).getUserName() + "  " +
				listPmpdAll.get(0).getProjectName() + "  " +
			listPmpdAll.get(0).getlistEvent().get(0).getCreateDate().toString();*/
		JSONArray jsonObject = JSONArray.fromObject(userlist);
		return jsonObject.toString();	
	}
	
	//get one user of inputting userid
	
	private UserDO CreateOneUser(ResultSet dbRs) throws SQLException{
		
		dbRs.next();
		UserDO userdo = new UserDO();
		userdo.setUserId(dbRs.getInt("userid"));
		userdo.setUserName(dbRs.getString("username"));
		userdo.setPassword(dbRs.getString("password"));
		userdo.setEmail(dbRs.getString("email"));
		userdo.setJoinDate(dbRs.getString("joindate"));
		return userdo;
	}
	
	//get one event json
	
	private String CreateOneUserJson(UserDO userdo){
		JSONObject jsonObject = JSONObject.fromObject(userdo);
		return jsonObject.toString();
	}
}
